// 在您的lib/db.js文件中

const mysql = require("mysql");
const db = require("../modal/db.config");

// 从config中获取数据库连接信息

class MySQLService {
  constructor() {
    this.pool = mysql.createPool({
      host: db.sql.host,
      port: db.sql.port,
      connectionLimit: db.sql.connectionLimit,
      user: db.sql.user,
      password: db.sql.password,
      database: db.sql.database,
    });
  }

  getConnect() {
    return new Promise((resolve, reject) => {
      this.pool.getConnection(function (err, connection) {
        if (err) {
          reject(err);
        } else {
          resolve(connection);
        }
      });
    });
  }

  // 插入单条数据
  async insert(table, data) {
    return new Promise(async (resolve, reject) => {
      const keys = Object.keys(data).join(",");
      const values = Object.keys(data)
        .map(() => "?")
        .join(",");
      const sql = `INSERT INTO ${table} (${keys}) VALUES (${values})`;

      try {
        const connection = await this.getConnect();
        await connection.query(sql, Object.values(data), (err, res) => {
          if (err) reject(err);
          if (res) resolve(res);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }

  // 根据条件查询
  async select(
    table,
    { where = {}, like = {}, limit = null, offset = null, orders = [] }
  ) {
    return new Promise(async (resolve, reject) => {
      let sql = `SELECT * FROM ${table}`;
      const placeholders = [];
      // 精准条件
      let whereClause = "";
      for (const key in where) {
        if (
          where[key] !== null &&
          where[key] !== "" &&
          where[key] !== undefined
        ) {
          if (whereClause.length > 0) {
            whereClause += " AND ";
          }
          whereClause += `${key} = ?`;
          placeholders.push(where[key]);
        }
      }

      // 模糊条件
      let likeClause = "";
      for (const key in like) {
        if (like[key]) {
          if (likeClause.length > 0) {
            likeClause += " AND ";
          }
          likeClause += `${key} LIKE ?`;
          placeholders.push(`%${like[key]}%`);
        }
      }

      if (whereClause || likeClause) {
        sql += " WHERE ";
        if (whereClause) {
          sql += whereClause;
          if (likeClause) {
            sql += " AND ";
          }
        }
        if (likeClause) {
          sql += likeClause;
        }
      }

      // 排序方式
      if (Array.isArray(orders) && orders.length > 0) {
        sql += " ORDER BY ";
        sql += orders
          .map((order) => `${order.field} ${order.direction.toUpperCase()}`)
          .join(", ");
      }

      // 限制数量与偏移量
      if (limit !== null) {
        if (offset !== null) {
          sql += ` LIMIT ?, ?`;
          placeholders.push(offset, limit);
        } else {
          sql += ` LIMIT ?`;
          placeholders.push(limit);
        }
      }
      try {
        const connection = await this.getConnect();
        connection.query(sql, placeholders, (err, res) => {
          if (err) reject(err);
          if (res) resolve(res);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }

  // 查询全部
  findAll(table) {
    return new Promise(async (resolve, reject) => {
      const sql = `SELECT * FROM ${table}`;
      try {
        const connection = await this.getConnect();
        connection.query(sql, (err, res) => {
          if (err) reject(err);
          if (res) resolve(res);
        });
        connection.release();
      } catch (err) {
        reject(err);
        throw err;
      }
    });
  }
  //查单条
  async getOne(table, condition = {}) {
    return new Promise(async (resolve, reject) => {
      let whereClause = "";
      const placeholders = [];
      for (const key in condition) {
        if (whereClause.length > 0) {
          whereClause += " AND ";
        }
        whereClause += `${key} = ?`;
        placeholders.push(condition[key]);
      }

      const sql = `SELECT * FROM ${table} WHERE ${whereClause} LIMIT 1`;

      try {
        const connection = await this.getConnect();
        await connection.query(sql, placeholders, (err, results) => {
          if (err) reject(err);
          if (results) resolve(results.length > 0 ? results[0] : null);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }

  // 根据条件统计总条数（支持精准条件、模糊条件）
  async count(table, { where = {}, like = {} }) {
    return new Promise(async (resolve, reject) => {
      let sql = `SELECT COUNT(*) AS total_count FROM ${table}`;
      const placeholders = [];
      // 精准条件
      let whereClause = "";
      for (const key in where) {
        if (
          where[key] !== null &&
          where[key] !== "" &&
          where[key] !== undefined
        ) {
          if (whereClause.length > 0) {
            whereClause += " AND ";
          }
          whereClause += `${key} = ?`;
          placeholders.push(where[key]);
        }
      }

      // 模糊条件
      let likeClause = "";
      for (const key in like) {
        if (likeClause.length > 0) {
          likeClause += " AND ";
        }
        likeClause += `${key} LIKE ?`;
        placeholders.push(`%${like[key]}%`);
      }

      if (whereClause || likeClause) {
        sql += " WHERE ";
        if (whereClause) {
          sql += whereClause;
          if (likeClause) {
            sql += " AND ";
          }
        }
        if (likeClause) {
          sql += likeClause;
        }
      }

      try {
        const connection = await this.getConnect();
        connection.query(sql, placeholders, (err, results) => {
          if (err) reject(err);
          if (results) resolve(results.length > 0 ? results[0].total_count : 0);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }

  // 更新数据
  async update(table, data, condition) {
    return new Promise(async (resolve, reject) => {
      let setClause = "";
      const placeholders = [];
      for (const key in data) {
        setClause += `${key} = ?, `;
        placeholders.push(data[key]);
      }
      setClause = setClause.slice(0, -2); // 去掉最后一个逗号

      let whereClause = "";
      for (const key in condition) {
        whereClause += `${key} = ? AND `;
        placeholders.push(condition[key]);
      }
      whereClause = whereClause.slice(0, -5); // 去掉最后一个 "AND "

      const sql = `UPDATE ${table} SET ${setClause} WHERE ${whereClause}`;

      try {
        const connection = await this.getConnect();
        connection.query(sql, [...placeholders], (err, results) => {
          if (err) reject(err);
          if (results) resolve(results);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }

  // 删除数据
  async delete(table, condition) {
    return new Promise(async (resolve, reject) => {
      let whereClause = "";
      const placeholders = [];
      for (const key in condition) {
        whereClause += `${key} = ? AND `;
        placeholders.push(condition[key]);
      }
      whereClause = whereClause.slice(0, -5); // 去掉最后一个 "AND "

      const sql = `DELETE FROM ${table} WHERE ${whereClause}`;

      try {
        const connection = await this.getConnect();
        connection.query(sql, placeholders, (err, results) => {
          if (err) reject(err);
          if (results) resolve(results);
        });
        connection.release();
      } catch (err) {
        throw err;
      }
    });
  }
}

module.exports = MySQLService;
